/* 
 * Copyright (C) 2012 UTSChedule
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package utsc.apps.utschedule.util.database;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.Callable;

import com.j256.ormlite.android.apptools.OpenHelperManager;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.Dao.CreateOrUpdateStatus;
import com.j256.ormlite.dao.ForeignCollection;
import com.j256.ormlite.misc.TransactionManager;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.stmt.Where;

import utsc.apps.utschedule.classes.Assignment;
import utsc.apps.utschedule.classes.Course;
import utsc.apps.utschedule.classes.Exam;
import utsc.apps.utschedule.classes.GenericTask;
import utsc.apps.utschedule.classes.Timetable;
import utsc.apps.utschedule.util.TimetableHelper;
import utsc.apps.utschedule.util.provider.SearchProvider;

import android.app.SearchManager;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;
import android.util.Log;

/**
 * A utility class to manage all database related DAO operations. 
 *
 */
public class DatabaseManager {
	
	private static DatabaseManager _instance;
	private static boolean _released;
	private DatabaseHelper _helper;
	public static String DEBUG_TAG="utsc.apps.utschedule.util.database.DatabaseManager";
	private static final String FTS_VIRTUAL_SUGGESTION_TABLE="FTSsuggestion";
	
	/**
	 * Returns a single instance of the manager.
	 * @param context
	 * @return
	 */
	public static DatabaseManager getInstance(Context context){
		//if (_instance==null) {
			_instance=new DatabaseManager(context);
		//}
		return _instance;
	}
	
	private DatabaseManager(Context context){
		_helper=OpenHelperManager.getHelper(context, DatabaseHelper.class);//new DatabaseHelper(context.getApplicationContext());
	}
	
	/**
	 * Return all {@link Timetable timetables} in a list by using the ORMLite {@link Dao} class.
	 * @return
	 */
	public List<Timetable> getAllTimetables(){
		List<Timetable> timetables=null;
		try {
			timetables=_helper.get_timetableDao().queryForAll();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return timetables;
	}
	
	/**
	 * Return all the {@link GenericTask tasks} of the given course id.
	 * @param courseId -the course id we are looking for
	 * @return
	 */
	public List<GenericTask> getCourseTasks(int courseId) {
		Dao<Assignment, Integer> assignmentDao=_helper.get_assignmentDao();
		Dao<Exam, Integer> examDao=_helper.get_examDao();
		try {
			List<Assignment> assignments=assignmentDao.queryForEq(Assignment.COURSE, courseId);
			List<Exam> exams=examDao.queryForEq(Exam.COURSE, courseId);
			
			List<GenericTask> tasks=new ArrayList<GenericTask>();
			if (assignments!=null && !assignments.isEmpty()) {
				tasks.addAll(assignments);
			}
			
			if (exams!=null && !exams.isEmpty()) {
				tasks.addAll(exams);
			}
			return tasks;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Return a {@link Timetable timetable} of the given timetable id.
	 * @param id -the input timetable id
	 * @return
	 */
	public Timetable getTimetableByID(int id) {
		Timetable t=null;
		try {
			t=_helper.get_timetableDao().queryForId(id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return t;
	}
	
	/**
	 * Return the course id that the given assignment id belongs to.
	 * @param assignmentId
	 * @return
	 */
	public int getCourseIdByAssignmentId(int assignmentId){
		if (assignmentId!=0) {
			Dao<Assignment, Integer> dao=_helper.get_assignmentDao();
			try {
				return dao.queryForId(assignmentId).get_course().get_courseID(); 
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	/**
	 * Return a list of {@link Assignment assignments} of the given parent course id.
	 * @param parentCourseId
	 * @return
	 */
	public List<Assignment> queryAssignment(int parentCourseId){
		Dao<Assignment, Integer> dao=_helper.get_assignmentDao();
		try {
			return dao.queryForEq(Assignment.COURSE, parentCourseId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Return a list of {@link Exam exams} of the given parent course id.
	 * @param parentCourseId
	 * @return
	 */
	public List<Exam> queryExams(int parentCourseId){
		Dao<Exam, Integer> dao=_helper.get_examDao();
		try {
			return dao.queryForEq(Exam.COURSE, parentCourseId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Delete an {@link Assignment assignment} by the given assignment id.
	 * @param assignmentId
	 */
	public void deleteAssignmentById(int assignmentId){
		Dao<Assignment, Integer> dao=_helper.get_assignmentDao();
		try {
			dao.deleteById(assignmentId);
			dao.getObjectCache().remove(Assignment.class, assignmentId);
			deleteSuggestion(SearchProvider.ASSIGNMENTS, assignmentId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Return a list of {@link Course courses} without tutorials and dependents.
	 * @param timetableID
	 * @return
	 */
	public List<Course> getCoursesWithoutTutorials(int timetableID) {
		Dao<Course, Integer> dao=_helper.get_courseDao();
		QueryBuilder<Course, Integer> courseQueryBuilder=dao.queryBuilder().orderBy(Course.COURSE_CODE, true);
		Where<Course, Integer> where=courseQueryBuilder.where();
		List<Course>courses=null;
		try {
			dao.clearObjectCache();
			courses=where.eq(Course.TIMETABLE, timetableID).and().eq(Course.COURSE_ID, Course.PARENT_COURSE_ID).and().eq(Course.TUTORIAL, false).query();
		} catch (SQLException e) {
			e.printStackTrace();
			courses=null;
		}
		return courses;
	}
	
	/**
	 * Return a count of total {@link Course courses} that are not tutorials or dependents.
	 * @param timetableID
	 * @return
	 */
	public int countCoursesWithoutTutorials(int timetableID){
		Dao<Course, Integer> dao=_helper.get_courseDao();
		QueryBuilder<Course, Integer> courseQueryBuilder=dao.queryBuilder().orderBy(Course.COURSE_CODE, true);
		courseQueryBuilder.setCountOf(true);
		Where<Course, Integer> where=courseQueryBuilder.where();
		try {
			return (int) dao.countOf(where.eq(Course.TIMETABLE, timetableID).and().eq(Course.COURSE_ID, Course.PARENT_COURSE_ID).and().eq(Course.TUTORIAL, false).prepare());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * Return an {@link Exam exam} of the given exam id.
	 * @param id
	 * @return
	 */
	public Exam getExam(int id){
		Dao<Exam, Integer> dao=_helper.get_examDao();
		try {
			return dao.queryForId(id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Return an {@link Assignment assignment} of the given assignment id
	 * @param id
	 * @return
	 */
	public Assignment getAssignment(int id){
		Dao<Assignment, Integer> dao=_helper.get_assignmentDao();
		try {
			return dao.queryForId(id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Return the current active {@link Timetable timetable}, which has a status = {@code Timetable.STATUS_ACTIVE}.
	 * @return
	 */
	public Timetable getCurrentActiveTimetable(){
		Dao<Timetable, Integer> dao=_helper.get_timetableDao();
		try {
			String sessionStr="Winter";
			switch ( TimetableHelper.getSession()) {
			case TimetableHelper.SESSION_WINTER:
				sessionStr="Winter";
				break;
			case TimetableHelper.SESSION_SUMMER:
				sessionStr="Summer";
				break;
			case TimetableHelper.SESSION_FALL:
				sessionStr="Fall";
				break;
			default:
				break;
			}
			Where<Timetable,Integer> where=dao.queryBuilder().where();
			where.eq(Timetable.STATUS, Timetable.STATUS_ACTIVE).and().eq(Timetable.SESSION,sessionStr);
			List<Timetable> timetables=dao.query(where.prepare());
			if (timetables!=null && !timetables.isEmpty()) {
				return timetables.get(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Return a list of {@link Course courses} which are not tutorials or
	 * dependents, and at the same time do not have any entries in the given
	 * exam type(midterm/final).
	 * 
	 * @param timetableID 
	 * @param examType either Midterm/Final
	 * @return
	 */
	public List<Course> getCoursesWithoutTutorialsAndExam(int timetableID,int examType) {
		Dao<Course, Integer> dao=_helper.get_courseDao();
		QueryBuilder<Course, Integer> courseQueryBuilder=dao.queryBuilder().orderBy(Course.COURSE_CODE, true).groupBy(Course.COURSE_CODE);
		Where<Course, Integer> where=courseQueryBuilder.where();
		List<Course>courses=null;
		try {
			courses=where.eq(Course.TIMETABLE, timetableID).and().eq(Course.TUTORIAL, false).query();
		} catch (SQLException e) {
			e.printStackTrace();
			courses=null;
		}
		return courses;
	}
	
	/**
	 * Return all the {@link Exam exams} of the given {@link Timetable timetable}.
	 * @param timetableID
	 * @return
	 */
	public List<Exam> getExamList(int timetableID) {
		Dao<Exam, Integer>dao=_helper.get_examDao();
		QueryBuilder<Exam, Integer> examQueryBuilder=dao.queryBuilder().orderBy(Exam.TYPE, true).orderBy(Exam.STARTTIME, true);
		Where<Exam, Integer> where=examQueryBuilder.where();
		List<Exam>exams=null;
		try {
			exams=where.eq(Exam.TIMETABLE, timetableID).query();
			if (!exams.isEmpty()&&exams.get(0).get_course().toString()==null) {
				Dao<Course, Integer> courseDao=_helper.get_courseDao();
				long now=System.currentTimeMillis();
				for (Exam exam : exams) {
					if (now>exam.get_endTime()) {
						exam.set_expired(true);
					}
					courseDao.refresh(exam.get_course());
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			exams=null;
		}
		return exams;
	}
	
	/**
	 * Save a list of {@link Timetable timetables} in one transaction.
	 * @param timetables
	 */
	public void saveTimetables(final List<Timetable> timetables) {
		try {
			TransactionManager.callInTransaction(_helper.getConnectionSource(), new Callable<Void>() {
				
				@Override
				public Void call() throws Exception {
					Dao<Timetable, Integer>dao=_helper.get_timetableDao();
					for (Timetable timetable : timetables) {
						dao.createOrUpdate(timetable);
					}
					return null;
				}
			});
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Return a {@link Course course} by the given course id.
	 * @param id
	 * @return
	 */
	public Course getCourse(int id) {
		Dao<Course, Integer> dao=_helper.get_courseDao();
		try {
			return dao.queryForId(id);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * Release the underlying {@link DatabaseHelper db helper} and resources.
	 */
	public static void release() {
		if (_instance!=null) {
			if (_instance.get_helper()!=null) {
				OpenHelperManager.releaseHelper();
			}
			_instance.set_helper(null);
			
		}
		_instance=null;
		_released=true;
		Log.i(DEBUG_TAG, "released");
	}
	
	/**
	 * Clear off all the object cache created by all the {@link Dao Daos}.
	 */
	public void refreshObjectCache() {
		_helper.get_assignmentDao().getObjectCache().clearAll();
		_helper.get_courseDao().getObjectCache().clearAll();
		_helper.get_examDao().getObjectCache().clearAll();
		_helper.get_timetableDao().getObjectCache().clearAll();
	}
	
	public DatabaseHelper get_helper() {
		return _helper;
	}
	
	public void set_helper(DatabaseHelper _helper) {
		this._helper = _helper;
	}
	
	/**
	 * Save the given {@link Timetable timetable} to database.
	 * @param t
	 */
	public void saveTimetable(Timetable t){
		try {
			UpdateBuilder<Timetable, Integer>updateBuilder= _helper.get_timetableDao().updateBuilder();
			Dao<Timetable, Integer> dao=_helper.get_timetableDao();
			updateBuilder.updateColumnValue(Timetable.STATUS, Timetable.STATUS_INACTIVE);
			dao.update(updateBuilder.prepare());
			dao.createOrUpdate(t);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ForeignCollection<Course> getEmptyCourseCollection() {
		try {
			return _helper.get_timetableDao().getEmptyForeignCollection("courses");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Update the given {@link Course course}.
	 * @param course
	 */
	public void updateCourse(Course course){
		Dao<Course, Integer> dao=_helper.get_courseDao();
		try {
			dao.update(course);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Update the given {@link Assignment assignment}
	 * @param assignment
	 */
	public void updateAssignment(Assignment assignment){
		Dao<Assignment, Integer> dao=_helper.get_assignmentDao();
		try {
			dao.update(assignment);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static boolean is_released() {
		return _released;
	}
	
	/**
	 * Refresh the given {@link Timetable timetable}.
	 * @param t
	 */
	public void refreshTimetable(Timetable t){
		Dao<Timetable, Integer> dao=_helper.get_timetableDao();
		try {
			dao.refresh(t);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Return a list of {@link Timetable archive timetables}.
	 * @return
	 */
	public List<Timetable> getArchiveTimetables(){
		Dao<Timetable, Integer> dao=_helper.get_timetableDao();
		try {
			return dao.queryForEq(Timetable.STATUS, Timetable.STATUS_INACTIVE);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Return the {@link Course parent course} by the given course code and the timetable id to search from.
	 * @param courseCode
	 * @param timetableID
	 * @return
	 */
	public Course queryParentCourse(String courseCode,int timetableID){
		Dao<Course, Integer> dao=_helper.get_courseDao();
		try {
			QueryBuilder<Course, Integer> queryBuilder=dao.queryBuilder();
			Where<Course, Integer> where=queryBuilder.where().like(Course.COURSE_CODE, "%"+courseCode+"%").and().eq(Course.COURSE_ID, Course.PARENT_COURSE_ID).and().eq(Course.TIMETABLE, timetableID);
			return dao.queryForFirst(where.prepare());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Save/Update the given {@link Course course} into database, it also inserts/updates an entry to the search suggestion table.
	 * @param course
	 */
	public void saveCourse(Course course){
		Dao<Course, Integer> dao=_helper.get_courseDao();
		CreateOrUpdateStatus status;
		try {
			status = dao.createOrUpdate(course);
			if (status.isCreated()) {
				//if it was an insert, simply add a new entry to the search suggestion
				addSuggestion(course);							
			}else {
				//if it was a update, update the associated assignments and exams
				Dao<Assignment, Integer>assignmentDao=_helper.get_assignmentDao();
				Dao<Exam, Integer>examDao=_helper.get_examDao();
				
				HashMap<String, Object>args=new HashMap<String, Object>();
				args.put(Assignment.COURSE, course);
				ArrayList<Assignment> assignments=new ArrayList<Assignment>(assignmentDao.queryForFieldValues(args));
				args.clear();
				args.put(Exam.COURSE, course);
				ArrayList<Exam> exams=new ArrayList<Exam>(examDao.queryForFieldValues(args));
				
				SQLiteDatabase db=_helper.getWritableDatabase();
				db.beginTransaction();
				updateCourseByParent(course);
				try {
					//refresh the search suggestions
					updateSuggestion(course);
					
					for (Assignment assignment : assignments) {
						updateSuggestion(assignment);
					}
					for (Exam exam : exams) {
						updateSuggestion(exam);
					}
					db.setTransactionSuccessful();
				} catch (Exception e) {
					e.printStackTrace();
				}finally{
					db.endTransaction();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * Update all the {@link Course courses} with the given {@link Course parent course}.
	 * @param course
	 */
	public void updateCourseByParent(Course course) {
		Dao<Course, Integer> dao=_helper.get_courseDao();
		UpdateBuilder<Course, Integer> updateBuilder=dao.updateBuilder();
		try {
			updateBuilder.updateColumnValue(Course.COURSE_CODE, course.get_courseCode());
			//updateBuilder.updateColumnValue(Course.SECTION, course.get_section());
			updateBuilder.updateColumnValue(Course.WEIGHT, course.get_weight());
			updateBuilder.where().eq(Course.PARENT_COURSE_ID, course.get_parentCourse().get_courseID());
			dao.update(updateBuilder.prepare());
			dao.clearObjectCache();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Save/Update a list of given {@link Course courses}.
	 * @param timetableid
	 * @param courses
	 */
	public void saveCourses(final int timetableid, final ArrayList<Course> courses) {
		try {
			TransactionManager.callInTransaction(_helper.getConnectionSource(), new Callable<Void>() {
				@Override
				public Void call() throws Exception {
					Dao<Course, Integer> dao=_helper.get_courseDao();
					Course parentCourse=null; 
					for (Course course : courses) {
						parentCourse=queryParentCourse(course.get_courseCode(),timetableid);
						if (parentCourse==null) {
							course.set_parentCourse(new Course(-1));
							dao.createOrUpdate(course);
							course.set_parentCourse(course);
						}else {
							course.set_parentCourse(parentCourse);
						}
						CreateOrUpdateStatus status=dao.createOrUpdate(course);
						if (status.isCreated()) {
							addSuggestion(course);							
						}
					}
					return null;
				}
			});
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Save the given {@link Course courses} into the database, and set a unique
	 * sync id to indicate these {@link Course courses} are from Intranet sync.
	 * 
	 * @param timetableid
	 * @param courses
	 */
	public void saveSyncCourse(final int timetableid, final ArrayList<Course> courses) {
		try {
			TransactionManager.callInTransaction(_helper.getConnectionSource(), new Callable<Void>() {
				@Override
				public Void call() throws Exception {
					Dao<Course, Integer> dao=_helper.get_courseDao();
					UpdateBuilder<Course, Integer> updateBuilder=dao.updateBuilder();
					QueryBuilder<Course, Integer> queryBuilder=dao.queryBuilder();
					Timetable table=new Timetable();
					table.set_timetableID(timetableid);
					Course parentCourse=null;
					for (Course course : courses) {
						course.set_timetable(table);
						updateBuilder.where().eq(Course.SYNC_ID, course.get_syncID()).and().eq(Course.TIMETABLE, timetableid);
						queryBuilder.where().eq(Course.SYNC_ID, course.get_syncID()).and().eq(Course.TIMETABLE, timetableid);
						updateBuilder.updateColumnValue(Course.COURSE_CODE, course.get_courseCode());
						updateBuilder.updateColumnValue(Course.DAY, course.get_day());
						updateBuilder.updateColumnValue(Course.INSTRUCTOR, course.get_instructor());
						updateBuilder.updateColumnValue(Course.SECTION, course.get_section());
						updateBuilder.updateColumnValue(Course.TUTORIAL, course.is_tutorial());
						updateBuilder.updateColumnValue(Course.ENDTIME, course.get_endTime());
						updateBuilder.updateColumnValue(Course.LOCATION, course.get_location());
						updateBuilder.updateColumnValue(Course.STARTTIME, course.get_startTime());
						
						int rowUpdated=dao.update(updateBuilder.prepare());
						if (rowUpdated==0) {
							parentCourse=queryParentCourse(course.get_courseCode(),timetableid);
							if (parentCourse==null) {
								course.set_parentCourse(new Course(-1));
								dao.create(course);
								course.set_parentCourse(course);
							}else {
								course.set_parentCourse(parentCourse);
							}
							dao.createOrUpdate(course);
							addSuggestion(course);							
						}else {
							//dao.queryForFirst(queryBuilder.prepare());
							updateSuggestion(dao.queryForFirst(queryBuilder.prepare()));
						}
					}
					dao.clearObjectCache();
					return null;
				}
			});
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Save the given {@link Exam exams} from Intranet sync into the database.
	 * 
	 * @param exams
	 */
	public void saveSyncExam( final ArrayList<Exam> exams) {
		try {
			TransactionManager.callInTransaction(_helper.getConnectionSource(), new Callable<Void>() {
				@Override
				public Void call() throws Exception {
					Dao<Exam, Integer> dao=_helper.get_examDao();
					UpdateBuilder<Exam, Integer> updateBuilder=dao.updateBuilder();
					QueryBuilder<Exam, Integer> queryBuilder=dao.queryBuilder();
					
					for (Exam exam : exams) {
						updateBuilder.where().eq(Exam.COURSE,exam.get_course().get_courseID()).and().eq(Exam.TYPE, exam.get_type());
						queryBuilder.where().eq(Exam.COURSE,exam.get_course().get_courseID()).and().eq(Exam.TYPE, exam.get_type());
						updateBuilder.updateColumnValue(Exam.ENDTIME, exam.get_endTime());
						updateBuilder.updateColumnValue(Exam.LOCATION, exam.get_location());
						updateBuilder.updateColumnValue(Exam.STARTTIME, exam.get_startTime());
						
						int rowUpdated=dao.update(updateBuilder.prepare());
						if (rowUpdated==0) {
							dao.create(exam);
							addSuggestion(exam);							
						}else {
							//dao.queryForFirst(queryBuilder.prepare());
							updateSuggestion(dao.queryForFirst(queryBuilder.prepare()));
						}
					}
					dao.clearObjectCache();
					return null;
				}
			});
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Delete the given {@link Course course}, it also deletes all dependents as well.
	 * @param course
	 */
	public void deleteCourse(final Course course) {
		if (course.get_courseID()!=course.get_parentCourse().get_courseID()) {
			deleteCourseSingle(course);
		}else {
			
			try {
				TransactionManager.callInTransaction(_helper.getConnectionSource(), new Callable<Void>() {
					@Override
					public Void call() throws Exception {
						Dao<Course, Integer>dao=_helper.get_courseDao();
						List<Course> childrenCourses=dao.queryBuilder().where().eq(Course.PARENT_COURSE_ID, course.get_courseID()).query();

						if (childrenCourses.size() > 1) {
							for (Course child : childrenCourses) {
								deleteCourseSingle(child);
							}
						}
						return null;
					}
				});
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Delete the given {@link Course course}, it only deletes the given course
	 * and its associated {@link Assignment assignments} and {@link Exam exams}.
	 * 
	 * @param course
	 */
	private void deleteCourseSingle(Course course) {
		Dao<Course, Integer>dao=_helper.get_courseDao();
		try {
			dao.delete(course);
			Dao<Assignment, Integer>assignmentDao=_helper.get_assignmentDao();
			Dao<Exam, Integer>examDao=_helper.get_examDao();
			
			HashMap<String, Object>args=new HashMap<String, Object>();
			args.put(Assignment.COURSE, course);
			ArrayList<Assignment> assignments=new ArrayList<Assignment>(assignmentDao.queryForFieldValues(args));
			args.clear();
			args.put(Exam.COURSE, course);
			ArrayList<Exam> exams=new ArrayList<Exam>(examDao.queryForFieldValues(args));
			
			SQLiteDatabase db=_helper.getWritableDatabase();
			db.beginTransaction();
			
			try {
				deleteSuggestion(SearchProvider.COURSES, course.get_courseID());
				
				for (Assignment assignment : assignments) {
					
					deleteSuggestion(SearchProvider.ASSIGNMENTS, assignment.get_AssUID());
				}
				assignmentDao.delete(assignments);
				for (Exam exam : exams) {
					deleteSuggestion(SearchProvider.EXAMS, exam.get_examId());
				}
				examDao.delete(exams);
				db.setTransactionSuccessful();
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				db.endTransaction();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	
	public void saveAssignment(final Assignment assignment) {
		try {
			Dao<Assignment, Integer> dao=_helper.get_assignmentDao();
			CreateOrUpdateStatus status=dao.createOrUpdate(assignment);
			if (status.isCreated()) {
				addSuggestion(assignment);
			}else {
				updateSuggestion(assignment);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Save the given exam object, depends on the condition ( where we only can have max 2 exams for each course),
	 * it returns <code><b>1</b></code> for success, <code><b>-1</b></code> for exceeding max limit, and <code><b>0</b></code> for error encountered.
	 * @param exam
	 * @return
	 */
	public int saveExam(final Exam exam) {
		try {
			Dao<Exam, Integer>dao=_helper.get_examDao();
			
			QueryBuilder<Exam, Integer> queryBuilder=dao.queryBuilder().setCountOf(true);
			Where<Exam, Integer> where=queryBuilder.where().eq(Exam.COURSE, exam.get_course().get_courseID()).and().eq(Exam.TYPE, exam.get_type()).and().eq(Exam.TIMETABLE, exam.get_timetable().get_timetableID());
			if (dao.idExists(exam.get_examId())||dao.countOf(where.prepare())==0) {
				CreateOrUpdateStatus status=dao.createOrUpdate(exam);
				dao.getObjectCache().clear(Exam.class);
				if (status.isCreated()) {
					addSuggestion(exam);
				}else {
					updateSuggestion(exam);
				}
				return 1;
			}else {
				return -1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public void deleteExam(int examId) {
		Dao<Exam, Integer> dao=_helper.get_examDao();
		try {
			dao.deleteById(examId);
			deleteSuggestion(SearchProvider.EXAMS, examId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	private static final HashMap<String,String> _suggestionColumnMap = buildColumnMap();
	/**
     * Builds a map for all columns that may be requested, which will be given to the 
     * SQLiteQueryBuilder. This is a good way to define aliases for column names, but must include 
     * all columns, even if the value is the key. This allows the ContentProvider to request
     * columns w/o the need to know real column names and create the alias itself.
     */
    private static HashMap<String,String> buildColumnMap() {
        HashMap<String,String> map = new HashMap<String,String>();
        map.put(SearchManager.SUGGEST_COLUMN_TEXT_1, SearchManager.SUGGEST_COLUMN_TEXT_1);
        map.put(SearchManager.SUGGEST_COLUMN_TEXT_2, SearchManager.SUGGEST_COLUMN_TEXT_2);
        map.put(BaseColumns._ID, "rowid AS " +
                BaseColumns._ID);
        map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, 
                SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
        map.put(SearchManager.SUGGEST_COLUMN_INTENT_ACTION,
                SearchManager.SUGGEST_COLUMN_INTENT_ACTION);
        map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA,
                SearchManager.SUGGEST_COLUMN_INTENT_DATA);
        map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "'"+SearchManager.SUGGEST_NEVER_MAKE_SHORTCUT+"' as "+SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);
        map.put("timetable_id","timetable_id");
        map.put("type","type");
        return map;
    }
	
	/*public Cursor querySuggestionMatches(String queryStr, int timetableID,String[] columns){
		String selectionString=FTS_VIRTUAL_SUGGESTION_TABLE+" MATCH ? ";
		String[] selectionArgs = new String[] {queryStr+"*"};
		return query(selectionString, selectionArgs, columns);
	}*/
	
    /**
     * Save a new entry to the search suggestion table.
     * @param model
     * @return
     */
	public long addSuggestion(Object model) {
		if (model!=null) {
	        ContentValues initialValues = getSuggestionContentValues(model);
	        
	        return _helper.getWritableDatabase().insert(FTS_VIRTUAL_SUGGESTION_TABLE, null, initialValues);
		}
		return -99;
    }
	
	/**
	 * Perform a full text searching query of the given query string and the
	 * matching timetable id. It returns a {@link Cursor} with the given
	 * columns.
	 * 
	 * @param queryStr
	 * @param timetableID
	 * @param columns
	 * @return
	 */
	public Cursor querySuggestionMatches(String queryStr, int timetableID, String[] columns){
		String sql=getSelectSuggestionSQL(SearchManager.SUGGEST_COLUMN_TEXT_1,columns,timetableID)+" UNION "+getSelectSuggestionSQL(SearchManager.SUGGEST_COLUMN_TEXT_2,columns,timetableID);
		String[] selectionArgs = new String[] {queryStr+"*",queryStr+"*"};
		return query(sql, selectionArgs);
	}
	
	/**
	 * Build a select statement by using the input column name in the where
	 * condition with return the data from the input columns.
	 * 
	 * @param column
	 * @param columns
	 * @param timetableID
	 * @return
	 */
	private String getSelectSuggestionSQL(String column,String [] columns, int timetableID){
		String selections=null;
		StringBuilder sBuilder=new StringBuilder();
		for (String string : columns) {
			sBuilder.append(_suggestionColumnMap.get(string)).append(",");
		}
		selections=sBuilder.toString().substring(0, sBuilder.length()-1);
		return "SELECT "+selections+" FROM "+FTS_VIRTUAL_SUGGESTION_TABLE+" WHERE "+column+" MATCH ? AND timetable_id="+timetableID;
	}
	
	/**
	 * Return a list of {@link Course parent courses} by the given query string.
	 * @param queryStr
	 * @param timetableID
	 * @param columns
	 * @return
	 */
	public Cursor queryParentCourse(String queryStr, int timetableID, String[] columns) {
		String selection="_courseCode like ? and _courseID=_parentCourse_id and _timetable_id= ?";
		String[] selectionArgs = new String[] {"%"+queryStr+"%",String.valueOf(timetableID)};
		return _helper.getReadableDatabase().query("course", columns, selection, selectionArgs, null, null, Course.COURSE_CODE);
	}
	
	/**
	 * Update the search suggestion with the given entry.
	 * @param model
	 * @return
	 */
	public long updateSuggestion(Object model){
		ContentValues contentValues=getSuggestionContentValues(model);
		if (contentValues!=null) {
			String where="type MATCH ?";
			String[] args=null;
			if (model instanceof Course) {
				args=new String[]{"course"+((Course)model).get_courseID()};
			}else if (model instanceof Assignment) {
				args=new String[]{"assignment"+((Assignment)model).get_AssUID()};
			}else if (model instanceof Exam) {
				args=new String[]{"exam"+((Exam)model).get_examId()};
			}
			return _helper.getWritableDatabase().update(FTS_VIRTUAL_SUGGESTION_TABLE, contentValues, where, args);
		}
		return -99;
	}
	
	@SuppressWarnings("unused")
	private long deleteSuggestion(Object model){
		if (model==null) {
			return -99;
		}
		int type=0;
		int id=0;
		if (model instanceof Course) {
			type=SearchProvider.COURSES;
			id=((Course)model).get_courseID();
		}else if (model instanceof Assignment) {
			type=SearchProvider.ASSIGNMENTS;
			id=((Assignment)model).get_AssUID();
		}else if (model instanceof Exam) {
			type=SearchProvider.EXAMS;
			id=((Exam)model).get_examId();
		}
		return deleteSuggestion(type, id);
	}
	
	private long deleteSuggestion(int type, int id){
		if (id<=0 || type<=0) {
			return -99;
		}
		String where="type MATCH ?";//SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID+"='?' AND type='?'";
		String[] args=null;
		if (type == SearchProvider.COURSES) {
			args=new String[]{"course"+id};
		}else if (type == SearchProvider.ASSIGNMENTS) {
			args=new String[]{"assignment"+id};
		}else if (type == SearchProvider.EXAMS) {
			args=new String[]{"exam"+id};
		}
		return _helper.getWritableDatabase().delete(FTS_VIRTUAL_SUGGESTION_TABLE, where, args);
	}
	
	private long deleteSuggestions(int timetableId){
		if (timetableId<=0) {
			return -99;
		}
		String where="timetable_id match ?";
		String[] args=new String[]{timetableId+""};
		return _helper.getWritableDatabase().delete(FTS_VIRTUAL_SUGGESTION_TABLE, where, args);
	}
	
	public void	resetTimetable(final Timetable timetable){
		if (timetable!=null) {
			try {
				TransactionManager.callInTransaction(_helper.getConnectionSource(), new Callable<Void>() {
					@Override
					public Void call() throws Exception {
						ArrayList<Course> courses=timetable.get_courseList();
						
						Dao<Course, Integer> courseDao=_helper.get_courseDao();
						//Dao<Timetable, Integer> timetableDao=_helper.get_timetableDao();
						Dao<Assignment, Integer> assignmentDao=_helper.get_assignmentDao();
						Dao<Exam, Integer> examDao=_helper.get_examDao();
						for (Course course : courses) {
							assignmentDao.delete(course.get_assignmentList());
						}
						examDao.delete(getExamList(timetable.get_timetableID()));
						courseDao.delete(courses);
						
						assignmentDao.clearObjectCache();
						examDao.clearObjectCache();
						courseDao.clearObjectCache();

						int timetableid=timetable.get_timetableID();
						//timetableDao.deleteById(timetable.get_timetableID());
						deleteSuggestions(timetableid);
						return null;
						
					}
				}
				);
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private ContentValues getSuggestionContentValues(Object model){
		if (model!=null) {
	        ContentValues initialValues = new ContentValues();
	        
	        if (model instanceof Course) {
	        	final Course course=(Course) model;
	        	refreshTimetable(course.get_timetable());
	        	initialValues.put(SearchManager.SUGGEST_COLUMN_TEXT_1, course.toString());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_TEXT_2, course.get_timetable().toString());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, course.get_courseID());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA, buildSearchDataURI("courses")+"/"+course.get_courseID());
		        initialValues.put("timetable_id",course.get_timetable().get_timetableID());
		        initialValues.put("type", "course"+course.get_courseID());
			}else if (model instanceof Assignment) {
				final Assignment assignment=(Assignment) model;
				refreshCourse(assignment.get_course());
				refreshTimetable(assignment.get_course().get_timetable());
				initialValues.put(SearchManager.SUGGEST_COLUMN_TEXT_1, assignment.get_Title());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_TEXT_2, assignment.get_course().toString());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, assignment.get_AssUID());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA, buildSearchDataURI("assignments")+"/"+assignment.get_AssUID());
		        initialValues.put("timetable_id",assignment.get_course().get_timetable().get_timetableID());
		        initialValues.put("type", "assignment"+assignment.get_AssUID());
			}else if (model instanceof Exam) {
				final Exam exam=(Exam) model;
				refreshCourse(exam.get_course());
				refreshTimetable(exam.get_timetable());
				initialValues.put(SearchManager.SUGGEST_COLUMN_TEXT_1, exam.get_course().toString()+" "+exam.getTypeString());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_TEXT_2, exam.get_location());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, exam.get_examId());
		        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA, buildSearchDataURI("exams")+"/"+exam.get_examId());
		        initialValues.put("timetable_id",exam.get_timetable().get_timetableID());
		        initialValues.put("type", "exam"+exam.get_examId());
			}
	        initialValues.put(SearchManager.SUGGEST_COLUMN_INTENT_ACTION, Intent.ACTION_VIEW);
	        return initialValues;
		}
		return null;
	}
	
	private String buildSearchDataURI(String data){
		if (data!=null) {
			return "content://"+SearchProvider.AUTHORITY+"/"+data;
		}
		return data;
	}
	
	public void refreshCourse(Course course) {
		try {
			_helper.get_courseDao().refresh(course);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private Cursor query(String sql, String[] selectionArgs){
		return _helper.getReadableDatabase().rawQuery(sql, selectionArgs);
	}
	
	/**
     * Performs a database query.
     * @param selection The selection clause
     * @param selectionArgs Selection arguments for "?" components in the selection
     * @param columns The columns to return
     * @return A Cursor over all rows matching the query
     */
    @SuppressWarnings("unused")
	private Cursor query(String selection, String[] selectionArgs, String[] columns) {
        /* The SQLiteBuilder provides a map for all possible columns requested to
         * actual columns in the database, creating a simple column alias mechanism
         * by which the ContentProvider does not need to know the real column names
         */
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(FTS_VIRTUAL_SUGGESTION_TABLE);
        builder.setProjectionMap(_suggestionColumnMap);

        Cursor cursor = builder.query(_helper.getReadableDatabase(),
                columns, selection, selectionArgs, null, null, null);

        if (cursor == null) {
            return null;
        } else if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }
        return cursor;
    }

}
